﻿using System;
using System.Collections.Generic;
using System.Text;
using MeTalker.Model;
using System.Data.SqlClient;
using MeTalker.DBUtility;
using System.Data;

namespace MeTalker.DAL
{
    public class MembersDAL : DALBase
    {
        public static void Heart(string sessionId, string userName)
        {
            try
            {
                string sql = "Update Members set LatestOnline=@LatestOnline, LoginFlag=1 where UserName=@UserName and SoftID=@SoftID ";
                SqlParameter paramLatestOnline = new SqlParameter("@LatestOnline", SqlDbType.DateTime, 8, "LatestOnline");
                paramLatestOnline.Value = DateTime.Now;

                SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 100, "UserName");
                paramUserName.Value = userName;

                //SqlParameter SessionId = new SqlParameter("@SessionId", SqlDbType.VarChar, 100, "SessionId");
                //SessionId.Value = sessionId;
                SqlParameter paramSoftID = new SqlParameter("@SoftID", SqlDbType.VarChar, 100, "SoftID");
                paramSoftID.Value = sessionId;
                SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, new SqlParameter[] { paramLatestOnline, paramUserName, paramSoftID });
            }
            catch 
            {
            }
        }

        public static bool CheckEmail(string email)
        {
            try
            {
                string sql = "Select * from Members where Email='" + email + "'";// and LoginFlag=0
                if (SqlHelper.ExecuteDataTable(sql, null).Rows.Count > 0)
                    return true;
            }
            catch
            {
            }
            return false;
        }

        public static bool CheckUserName(string userName, out int errcode)
        {
            errcode = 0;
            try
            {
                string sql = "Select * from Members where UserName='" + userName + "' ";// and LoginFlag=0
                if (SqlHelper.ExecuteDataTable(sql, null).Rows.Count > 0)
                    return true;
            }
            catch
            {
                errcode = DATA_ERROR;
            }
            return false;
        }
        public static bool CheckUserName(string userName,string softId, out int errcode)
        {
            errcode = 0;
            try
            {
                string sql = "Select * from Members where UserName='" + userName + "' and softId='" + softId + "' ";// and LoginFlag=0
                if (SqlHelper.ExecuteDataTable(sql, null).Rows.Count > 0)
                    return true;
            }
            catch
            {
                errcode = DATA_ERROR;
            }
            return false;
        }
        public static bool CheckHDID(string hdid)
        {
            try
            {
                string sql = "Select * from Members where HDID='" + hdid + "'";// and LoginFlag=0
                if (SqlHelper.ExecuteDataTable(sql, null).Rows.Count > 0)
                    return true;
            }
            catch
            {
            }
            return false;
        }

        public static int Insert(MembersMod members)
        {
            try
            {
                //string sql = "insert into Members(SessionId,AddDate,Email,ExpireDate,HDID,IsValid,LatestOnline,LoginFlag,Memo,MobilePhone,Password,QQ,RegistDate,SoftID,SoftName,UserName,UserType) values(@SessionId, @AddDate,@Email,@ExpireDate,@HDID,@IsValid,@LatestOnline,@LoginFlag,@Memo,@MobilePhone,@Password,@QQ,@RegistDate,@SoftID,@SoftName,@UserName,@UserType)";
                string sql = "insert into Members(SessionId, AddDate,Email,ExpireDate,HDID,IsValid,LoginFlag,Memo,MobilePhone,Password,QQ,RegistDate,SoftID,SoftName,UserName,UserType) values(@SessionId, @AddDate,@Email,@ExpireDate,@HDID,@IsValid,@LoginFlag,@Memo,@MobilePhone,@Password,@QQ,@RegistDate,@SoftID,@SoftName,@UserName,@UserType)";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, GetSqlParameter(members));
            }
            catch
            {
                return 0;
            }
        }
        public static int Update(MembersMod members)
        {
            try
            {
                //string sql = "insert into Members(SessionId,AddDate,Email,ExpireDate,HDID,IsValid,LatestOnline,LoginFlag,Memo,MobilePhone,Password,QQ,RegistDate,SoftID,SoftName,UserName,UserType) values(@SessionId, @AddDate,@Email,@ExpireDate,@HDID,@IsValid,@LatestOnline,@LoginFlag,@Memo,@MobilePhone,@Password,@QQ,@RegistDate,@SoftID,@SoftName,@UserName,@UserType)";
                string sql = "Update Members set SessionId=@SessionId, AddDate=@AddDate,Email=@Email,ExpireDate=@ExpireDate,HDID=@HDID,IsValid=@IsValid,LoginFlag=@LoginFlag,Memo=@Memo,MobilePhone=@MobilePhone,Password=@Password,QQ=@QQ,RegistDate=@RegistDate,SoftID=@SoftID,SoftName=@SoftName,UserName=@UserName,UserType=@UserType where AutoID=@AutoID";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, GetSqlParameter(members));
            }
            catch
            {
                return 0;
            }
        }
        public static void Logout(string uid, string sessionId)
        {
            try
            {
                string sql = "Update Members set LoginFlag=0 where UserName=@UserName and SoftID=@SoftID";
                SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 100, "UserName");
                paramUserName.Value = uid;
                SqlParameter paramSoftID = new SqlParameter("@SoftID", SqlDbType.VarChar, 100, "SoftID");
                paramSoftID.Value = sessionId;
                SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, new SqlParameter[] { paramUserName, paramSoftID });
            }
            catch { }
        }
        public static DataTable SignUser(MembersMod members)
        {
            try
            {
                string sql = "Select * from Members where Password=@Password and UserName=@UserName and SoftID=@SoftID";// and LoginFlag=0
                return SqlHelper.ExecuteDataTable(sql, GetSqlParameter(members));
            }
            catch
            {
                return null;
            }
        }
        public static DataTable GetUsers()
        {
            try
            {
                string sql = "Select * from Members";// and LoginFlag=0
                return SqlHelper.ExecuteDataTable(sql, null);
            }
            catch
            {
                return null;
            }
        }
        public static DataTable GetFilter(string filter)
        {
            try
            {
                string sql = "Select * from Members";// and LoginFlag=0
                if (!string.IsNullOrEmpty(filter)) {
                    sql += " where " + filter;
                }
                return SqlHelper.ExecuteDataTable(sql, null);
            }
            catch
            {
                return null;
            }
        }
        public static DataTable GetMembers(int autoID)
        {
            try
            {
                string sql = "Select * from Members where AutoID=@AutoID";// and LoginFlag=0

                SqlParameter paramAutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
                paramAutoID.Value = autoID;
                return SqlHelper.ExecuteDataTable(sql, new SqlParameter[] { paramAutoID});
            }
            catch
            {
                return null;
            }
        }

        public static DataTable GetUsers(MembersMod members)
        {
            try
            {
                string sql = "Select * from Members where SoftID=@SoftID and UserName=@UserName";// and LoginFlag=0

                SqlParameter paramSoftID = new SqlParameter("@SoftID", SqlDbType.VarChar, 100, "SoftID");
                paramSoftID.Value = members.SoftID;
                SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 100, "UserName");
                paramUserName.Value = members.UserName;
                //SqlParameter paramEmail = new SqlParameter("@Email", SqlDbType.VarChar, 100, "Email");
                //paramEmail.Value = members.Email;
                return SqlHelper.ExecuteDataTable(sql, new SqlParameter[] { paramSoftID, paramUserName });
            }
            catch
            {
                return null;
            }
        }
        public static DataTable CheckSoftHD(MembersMod members)
        {
            try
            {
                string sql = "Select * from Members where SoftID=@SoftID and HDID=@HDID";// and LoginFlag=0

                SqlParameter paramSoftID = new SqlParameter("@SoftID", SqlDbType.VarChar, 100, "SoftID");
                paramSoftID.Value = members.SoftID;
                SqlParameter paramHDID = new SqlParameter("@HDID", SqlDbType.VarChar, 200, "HDID");
                paramHDID.Value = members.HDID;
                //SqlParameter paramEmail = new SqlParameter("@Email", SqlDbType.VarChar, 100, "Email");
                //paramEmail.Value = members.Email;
                return SqlHelper.ExecuteDataTable(sql, new SqlParameter[] { paramSoftID, paramHDID });
            }
            catch
            {
                return null;
            }
        }
        public static DataTable SignUser(string useName, string pwd,string soft)
        {
            try
            {
                string sql = "Select * from Members where Password=@Password and UserName=@UserName and SoftID=@SoftID";// and LoginFlag=0
                SqlParameter paramPassword = new SqlParameter("@Password", SqlDbType.VarChar, 100, "Password");
                paramPassword.Value = pwd;
                SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 100, "UserName");
                paramUserName.Value = useName;
                SqlParameter paramSoftID = new SqlParameter("@SoftID", SqlDbType.VarChar, 100, "SoftID");
                paramSoftID.Value = soft;
                return SqlHelper.ExecuteDataTable(sql, new SqlParameter[] { paramPassword, paramUserName, paramSoftID });
            }
            catch
            {
                return null;
            }
        }

        public static int Delete(string autoId)
        {
            return Delete(int.Parse(autoId));
        }

        public static int Delete(int autoId)
        {
            try
            {
                string sql = "delete from Members where AutoID=@AutoID";
                SqlParameter paramAutoID = new SqlParameter("@AutoID", SqlDbType.Int, 1, "AutoID");
                paramAutoID.Value = autoId;
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, new SqlParameter[] { paramAutoID });
            }
            catch
            {
                return 0;
            }
        }

        public static int UpdateLoginState(MembersMod members)
        {
            try
            {
                string sql = "Update Members set LatestOnline='{0}',LoginFlag={1},SessionId='{2}' where AutoID={3} ";
                sql = string.Format(sql, members.LatestOnline.ToString("yyyy-MM-dd HH:mm:ss"), members.LoginFlag == true ? 1 : 0, members.SessionId, members.AutoID);
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, null);



                //SqlParameter paramAutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
                //paramAutoID.Value = members.AutoID;

                //SqlParameter paramLatestOnline = new SqlParameter("@LatestOnline", SqlDbType.DateTime, 16, "LatestOnline");
                //paramLatestOnline.Value = members.LatestOnline;

                //SqlParameter paramLoginFlag = new SqlParameter("@LoginFlag", SqlDbType.VarChar, 1, "LoginFlag");
                //paramLoginFlag.Value = members.LoginFlag;

                //return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, new SqlParameter[] { paramAutoID, paramLatestOnline, paramLoginFlag });
            }
            catch(Exception ex) {
                Console.WriteLine(ex.Message);
                return 0;
            }
        }

        //public static int Update(MembersMod members)
        //{
        //    try
        //    {
        //        string sql = "Update Members set SessionId=@SessionId,AddDate=@AddDate,Email=@Email,ExpireDate=@ExpireDate,HDID=@HDID,IsValid=@IsValid,LatestOnline=@LatestOnline,LoginFlag=@LoginFlag,Memo=@Memo,MobilePhone=@MobilePhone,Password=@Password,QQ=@QQ,RegistDate=@RegistDate,SoftID=@SoftID,SoftName=@SoftName,UserName=@UserName,UserType=@UserType where AutoID=@AutoID";
        //        return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, GetSqlParameter(members));
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        static SqlParameter[] GetSqlParameter(MembersMod members)
        {
            List<SqlParameter> paramss = new List<SqlParameter>();
            SqlParameter paramAutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
            paramAutoID.Value = members.AutoID;
            paramss.Add(paramAutoID);

            SqlParameter paramAddDate = new SqlParameter("@AddDate", SqlDbType.DateTime, 16, "AddDate");
            paramAddDate.Value = members.AddDate;
            paramss.Add(paramAddDate);

            SqlParameter paramEmail = new SqlParameter("@Email", SqlDbType.VarChar, 100, "Email");
            paramEmail.Value = members.Email;
            paramss.Add(paramEmail);

            SqlParameter paramExpireDate = new SqlParameter("@ExpireDate", SqlDbType.DateTime, 16, "ExpireDate");
            paramExpireDate.Value = members.ExpireDate;
            paramss.Add(paramExpireDate);


            SqlParameter paramHDID = new SqlParameter("@HDID", SqlDbType.VarChar, 100, "HDID");
            paramHDID.Value = members.HDID;
            paramss.Add(paramHDID);

            SqlParameter paramIsValid = new SqlParameter("@IsValid", SqlDbType.Bit, 1, "IsValid");
            paramIsValid.Value = members.IsValid;
            paramss.Add(paramIsValid);

            //SqlParameter paramLatestOnline = new SqlParameter("@LatestOnline", SqlDbType.DateTime, 16, "LatestOnline");
            //paramLatestOnline.Value = members.LatestOnline;
            //paramss.Add(paramLatestOnline);

            //SqlParameter paramLicenseDate = new SqlParameter("@LicenseDate", SqlDbType.DateTime, 8, "LicenseDate");
            //paramLicenseDate.Value = members.LicenseDate;
            //paramss.Add(paramLicenseDate);

            SqlParameter paramLoginFlag = new SqlParameter("@LoginFlag", SqlDbType.Bit, 1, "LoginFlag");
            paramLoginFlag.Value = members.LoginFlag;
            paramss.Add(paramLoginFlag);

            SqlParameter paramMemo = new SqlParameter("@Memo", SqlDbType.VarChar, 500, "Memo");
            paramMemo.Value = members.Memo;
            paramss.Add(paramMemo);

            SqlParameter paramMobilePhone = new SqlParameter("@MobilePhone", SqlDbType.VarChar, 100, "MobilePhone");
            paramMobilePhone.Value = members.MobilePhone;
            paramss.Add(paramMobilePhone);

            SqlParameter paramPassword = new SqlParameter("@Password", SqlDbType.VarChar, 100, "Password");
            paramPassword.Value = members.Password;
            paramss.Add(paramPassword);

            SqlParameter paramQQ = new SqlParameter("@QQ", SqlDbType.VarChar, 100, "QQ");
            paramQQ.Value = members.QQ;
            paramss.Add(paramQQ);

            SqlParameter paramRegistDate = new SqlParameter("@RegistDate", SqlDbType.DateTime, 16, "RegistDate");
            paramRegistDate.Value = members.RegistDate;
            paramss.Add(paramRegistDate);

            SqlParameter paramSoftID = new SqlParameter("@SoftID", SqlDbType.VarChar, 100, "SoftID");
            paramSoftID.Value = members.SoftID;
            paramss.Add(paramSoftID);


            SqlParameter paramSoftName = new SqlParameter("@SoftName", SqlDbType.VarChar, 100, "SoftName");
            paramSoftName.Value = members.SoftName;
            paramss.Add(paramSoftName);

            SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 100, "UserName");
            paramUserName.Value = members.UserName;
            paramss.Add(paramUserName);

            SqlParameter paramUserType = new SqlParameter("@UserType", SqlDbType.VarChar, 100, "UserType");
            paramUserType.Value = members.UserType;
            paramss.Add(paramUserType);


            SqlParameter paramSessionId = new SqlParameter("@SessionId", SqlDbType.VarChar, 100, "SessionId");
            paramSessionId.Value = members.SessionId;
            paramss.Add(paramSessionId);

            //SqlParameter paramIsActivate = new SqlParameter("@IsActivate", SqlDbType.Bit, 1, "IsActivate");
            //paramIsActivate.Value = members.IsActivate;
            //paramss.Add(paramIsActivate);

            return paramss.ToArray();
        }
    }
}